package com.efast.cafe.commponent.query.service.impl;



import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;

import com.efast.cafe.commponent.query.dao.QueryDao;
import com.efast.cafe.commponent.query.service.IQueryService;
import com.efast.cafe.framework.bean.QueryPageBase;
import com.efast.cafe.framework.bean.TemplateParameter;
import com.efast.cafe.framework.exception.CafeQueryException;
import com.efast.cafe.framework.exception.ServiceException;
import com.efast.cafe.framework.exception.SystemException;
import com.efast.cafe.framework.jdbc.JdbcDao;
import com.efast.cafe.framework.util.QueryStringUtil;
import com.efast.cafe.framework.util.SqlConstants;
import com.efast.cafe.framework.util.SqlFormateUtil;
import com.efast.cafe.portal.bean.query.AbstractFieldType;
import com.efast.cafe.portal.bean.query.ColumnFilter;
import com.efast.cafe.portal.bean.query.CommonTableConditionBean;
import com.efast.cafe.portal.bean.query.QueryBean;
import com.efast.cafe.portal.bean.query.QueryConditionBean;
import com.efast.cafe.portal.bean.query.QueryOpBean;
import com.efast.cafe.portal.bean.query.QueryParameterBean;
import com.efast.cafe.portal.bean.query.TableConditionBean;
import com.efast.cafe.portal.entity.query.TableView;
import com.efast.cafe.portal.entity.query.Template;
import com.efast.cafe.portal.remote.query.TemplateParseService;
import com.efast.cafe.util.SetUtil;
import com.efast.cafe.util.StringUtil;

@Service("defaultQueryService")
@Transactional
public class QueryServiceImpl implements IQueryService {
	private final static String ORDER_BY=	"ORDER BY";
	private final static String ORDER_STATUS=	"normal";
	@Autowired
	private QueryDao queryDao;
	@Autowired
	private TemplateParseService templateParseService;
	
	@Qualifier("jdbcDao")
	@Autowired
	private JdbcDao jdbcDao;
	/**
	 * 获得模板查询的结果集
	 * @param querys
	 * @return
	 */
	@Override
	public Object queryList(QueryBean querys) {
		//处理模板查询的对象，放在QueryBean中
		dealWithTemplateArea(querys);
		//拼装sql并验证
		String message = doValidation(querys.getTemplate());
		if (SqlFormateUtil.SUCCESS_MESSAGE.equals(message)) {
			parseSql(querys);
			//此处用接口根据不同数据源选择查询page
			return queryPageList(querys);
			
		}else{
			throw new CafeQueryException(message);
		}
	}
	
	/**
	 * 处理参数信息 返回模板查询的结果集
	 * @param querys
	 * @return
	 */
	public QueryPageBase queryPageList(QueryBean querys){
		QueryParameterBean param = new QueryParameterBean();
		QueryPageBase queryPageBase = new QueryPageBase();
		QueryConditionBean bean = querys.getQueryConditionBean();
//		AbstractFieldType.parameters.clear();
//		AbstractFieldType.index = 0; 
		if (bean != null) {
			if (bean.getStart() == null || bean.getLength() == null) {
				bean.setStart(0);
				bean.setLength(querys.getTemplate().getPagenation());
			}
			querys.setStart(bean.getStart());
			querys.setLength(bean.getLength());
			queryPageBase.setStart(bean.getStart());
			queryPageBase.setLength(bean.getLength());
			if (querys.getLength() == null || querys.getStart() == null) {
				querys.setLength(bean.getLength());
				querys.setStart(bean.getStart());
			}
			if (!QueryStringUtil.isEmpty(bean.getSortKey()) && !ORDER_STATUS.equals(bean.getSortOrder())) {
				hanleSortedData(querys);
			}
			if (!SetUtil.isEmpty(bean.getColumnFilter())) {
				hanleFilterData(param,querys);
			}
			handleWhereCondition(param,querys);
		}
		if(querys.getLength()!=null){
			querys.getParameter().setMaxRowsInNoPage(null);
		}
		Map<String, Object> map = new HashMap<>();
		map.put("querys", querys);
		map.put("parameter", querys.getParameter());
		if(querys.getStart() == null || querys.getLength()==null || querys.getLength()==0){//不分页
			queryPageBase.setResultList(queryDao.selectList(map));
			
		}else{
			queryPageBase.setStart(querys.getStart());
			queryPageBase.setLength(querys.getLength());
			queryPageBase.setResultList(queryDao.selectListByPage(map));
			queryPageBase.setTotalRows(queryDao.selectListCount(map));
		}
		
		return queryPageBase;
	}
	
	
	public QueryBean handleQueryOps(QueryBean querys){
		List<QueryOpBean> queryBeanOps = querys.getQueryConditionBean().getQueryOpBeans();
		if(!queryBeanOps.isEmpty()){
			TemplateParameter parameter = querys.getParameter();
			if(parameter==null){
				parameter = new TemplateParameter();
			}
			Map<String, Object> par = new HashMap<>();
			queryBeanOps.forEach(op ->{
				par.put(op.getKey().replaceAll("\\.", "_"), op.getValue());
			});
			parameter.setMap(par);
			querys.setParameter(parameter);
		}
		return querys;
	}
	
	/**
	 * 处理模糊查询
	 * @param querys
	 * @return
	 */
	public static QueryBean hanlelQuickSearch(QueryParameterBean param,QueryBean querys) {
		Template template= querys.getTemplate();
		QueryConditionBean bean = querys.getQueryConditionBean();
		List<String> keys = bean.getViewColumns();
		if (SetUtil.isEmpty(querys.getTableConditions())) {
			return querys;
		}
		String quickSearchCondition = " AND (1 != 1";
		for (TableConditionBean conditionBean : querys.getTableConditions()) {
			if (SqlConstants.FieldType.Number.equals(conditionBean.getFiledType()) || SqlConstants.FieldType.Boolean.equals(conditionBean.getFiledType())) {
				continue;
			}
			Boolean isContains = false;
			if (SetUtil.isEmpty(keys)) {
				isContains = true;
			} else {
				for (String key : keys) {
					if (1 != conditionBean.getQuick() && !key.equals(conditionBean.getKey())) {
						continue;
					} else {
						isContains = true;
						break;
					}
				}
			}
			
			if (isContains) {
				quickSearchCondition += " OR "+AbstractFieldType.covertSql(param,conditionBean.getKey(), bean.getFuzzyQueryValue(), SqlConstants.OpType.Like, SqlConstants.FieldType.Text);
			}
			
		}
		template.setWhereArea(template.getWhereArea() + quickSearchCondition+")");
		return querys;
	}
	
	/**
	 * 处理排序
	 * @param querys
	 * @return
	 */
	public QueryBean hanleSortedData(QueryBean querys) {
		String orderbyArea ="";
		QueryConditionBean bean = querys.getQueryConditionBean();
		/*String entity = bean.getSortEntity();*/
		String key = bean.getSortKey();
		key = StringUtil.cleanXSS(key);
		Template template= querys.getTemplate();
		orderbyArea = ORDER_BY+" "+QueryStringUtil.contractBindSql(null, key) +" "+ bean.getSortOrder();
		template.setOrderbyArea(orderbyArea);
		return querys;
	}
	
	/**
	 * 处理筛选
	 * @param querys
	 * @return
	 */
	public QueryBean hanleFilterData(QueryParameterBean param,QueryBean querys) {
		Template template= querys.getTemplate();
		QueryConditionBean bean = querys.getQueryConditionBean();
		if (SetUtil.isEmpty(bean.getColumnFilter())) {
			return querys;
		}
		StringBuffer buffer = new StringBuffer();
		for (ColumnFilter filter : bean.getColumnFilter()) {
			buffer.append(" AND (")
			.append(AbstractFieldType.covertSql(param,filter.getFilterKey(), filter.getFilterValue(), SqlConstants.OpType.Like, SqlConstants.FieldType.Text))
			.append(")");
		}
		template.setWhereArea(template.getWhereArea() + buffer.toString());
		return querys;
	}
	
	/**
	 * 根据QueryOpBean生成过滤条件
	 * @param querys
	 * @return
	 */
	public QueryBean handleQueryCondition(QueryParameterBean param,QueryBean querys) {
		QueryConditionBean list = querys.getQueryConditionBean();
		List<QueryOpBean> opBeans = list.getQueryOpBeans();
		list.setViewColumns(addViewColumn(querys.getTableViews()));
		if (!SetUtil.isEmpty(opBeans)) {
			for (QueryOpBean op : opBeans) {
				String value = op.getValue();
				CommonTableConditionBean bean = AbstractFieldType.handelCondition(param,op,false);
				op.setOpName(SqlConstants.OpType.getOpName(op.getOp()));
				op.setOpType(SqlConstants.OpType.getOp(op.getOp()));
				op.setBindSql(bean.getBindSql());
				op.setValue(value);
			}
		}
		
		return querys;
	}
	
	/**
	 * 处理where的过滤条件
	 * @param querys
	 * @return
	 */
	public QueryBean handleWhereCondition(QueryParameterBean param,QueryBean querys) {
		if (!QueryStringUtil.isEmpty(querys.getQueryConditionBean().getFuzzyQueryValue())) {
			hanlelQuickSearch(param,querys);
		}
		handleQueryCondition(param,querys);
		Template template = querys.getTemplate();
		template.setWhereArea(template.getWhereArea().replace("WHERE", "").replace("where", ""));
		querys.setTemplate(template);
		TemplateParameter parameter = new TemplateParameter();
		parameter.setMap(param.getParameters());
		if (querys.getLength() != null && querys.getStart() != null) {
			parameter.getMap().put("start", querys.getStart());
			parameter.getMap().put("length", querys.getLength());
			parameter.setPagenation(querys.getTemplate().getPagenation());
			parameter.setMaxRowsInNoPage(querys.getTemplate().getMaxRowsInNoPage());
		}
		querys.setParameter(parameter);
		return querys;
	}
	
	/**
	 * 添加tableView配置
	 * @param list
	 * @return
	 */
	private List<String> addViewColumn (List<TableView> list) {
		if (SetUtil.isEmpty(list)) {
			return null;
		}
		List<String> lists = new ArrayList<>();
		for (TableView tableView : list) {
			if (tableView.getDefaultView() == 1) {
				lists.add(tableView.getKey());
			}
		}
		return lists;
	}
	
	/**
	 * 简单验证sql的合法性
	 * @param template
	 * @return
	 */
	public String doValidation(Template template) {
		String sql = "";
		if (template == null) {
			return SqlFormateUtil.doValidation(sql);
		}
		sql = getCombineSql(template);
		return SqlFormateUtil.doValidation(sql);
	}
	/**
	 * 处理#{obj.field}
	 * @param template
	 * @return
	 */
	public QueryBean parseSql(QueryBean queryBean) {
		Template template = queryBean.getTemplate();
		String sql = getCombineSql(template);
		template.setWhereArea(QueryStringUtil.getNonEmpty(templateParseService.parseSql(sql).getTemplate().getWhereArea()));
		queryBean.setTemplate(template);
		return queryBean;
	}
	/**
	 * 获得拼接的sql
	 * @param template
	 * @return
	 */
	public String getCombineSql(Template template) {
		String sql = "";
		if (template == null) {
			return SqlFormateUtil.doValidation(sql);
		}
		StringBuffer buffer = new StringBuffer();
		buffer.append(QueryStringUtil.getNonNull(template.getSelectArea()))
		.append(" ")
		.append(QueryStringUtil.getNonNull(template.getFromArea()))
		.append(" ")
		.append(QueryStringUtil.getNonNull(template.getWhereArea()))
		.append(" ")
		.append(QueryStringUtil.getNonNull(template.getGroupbyArea()))
		.append(" ")
		.append(QueryStringUtil.getNonNull(template.getOrderbyArea()));
		sql = buffer.toString();
		return sql;
	}
	
	/**
	 * 处理转义符号
	 * @param querys
	 * @return
	 */
	public QueryBean dealWithTemplateArea(QueryBean querys) {
		if (querys == null || querys.getTemplate() == null) {
			return querys;
		}
		Template template = querys.getTemplate();
		template.setSelectArea(template.getSelectArea().replace("\\", ""));
		template.setFromArea(QueryStringUtil.getNonEmpty(template.getFromArea()));
		template.setOrderbyArea(QueryStringUtil.getNonEmpty(template.getOrderbyArea()));
		template.setGroupbyArea(QueryStringUtil.getNonEmpty(template.getGroupbyArea()));
		template.setWhereArea(QueryStringUtil.getNonEmpty(template.getWhereArea()));
		querys.setTemplate(template);
		return querys;
	}
	
	@Override
	public List<Map<String, Object>> queryMapBySql(StringBuffer sql,List<Object> params) {
		return jdbcDao.queryForListMap(sql.toString(), params);
	}
}
